﻿using BwTMusicModels;
using HwjUIDI.Utility.DatabaseHelper;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BwTMusicServices
{
    public class MusicServices
    {
        /// <summary>
        /// 添加音乐信息
        /// </summary>
        /// <param name="music"></param>
        /// <returns></returns>
        public int AddMusicInfo(North_Music music)
        {
            int bResult = 0;      
            try
            {
                string sql = $"insert into North_Music (MusicName,MusicSinger,MusicAlbum,MusicImgUrl,MusicCategory,MusicTag,MusicBitRate,MusicTime,MusicLong,MusicUrl) values('{music.MusicName}','{music.MusicSinger}','{music.MusicAlbum}','{music.MusicImgUrl}','{music.MusicCategory}','{music.MusicTag}','{music.MusicBitRate}','{music.MusicTime}','{music.MusicLong}','{music.MusicUrl}')";
                bResult = DatabaseFactory.GetDatabase().ExecNonQuerySQL(sql);
            }
            catch (Exception ex)
            {

            }
            return bResult;
        }
        /// <summary>
        /// 添加一个歌手
        /// </summary>
        /// <param name="singer"></param>
        /// <returns></returns>
        public int AddMusicSinger(North_Singer singer)
        {
            int bResult = 0;
            try
            {
                string sql = $"insert into North_Singer (SingerName,SingerCountry) values('{singer.SingerName}','{singer.SingerCountry}')";
                bResult = DatabaseFactory.GetDatabase().ExecNonQuerySQL(sql);
            }
            catch (Exception)
            {

            }
            return bResult;
        }
        /// <summary>
        /// 添加一个歌手音乐
        /// </summary>
        /// <param name="singermusic"></param>
        /// <returns></returns>
        public int AddSingerMusic(SingerMusic singermusic)
        {
            int bResult = 0;
            try
            {
                string sql = $"insert into SingerMusic (SingerId,MusicId) values('{singermusic.SingerId}','{singermusic.MusicId}')";
                bResult = DatabaseFactory.GetDatabase().ExecNonQuerySQL(sql);
            }
            catch (Exception)
            {

            }
            return bResult;
        }
        /// <summary>
        /// 添加一个歌手专辑
        /// </summary>
        /// <param name="singerAlbum"></param>
        /// <returns></returns>
        public int AddSingerAlbum(SingerAlbum singerAlbum)
        {
            int bResult = 0;
            try
            {
                string sql = $"insert into SingerAlbum (SingerId,AlbumId) values('{singerAlbum.SingerId}','{singerAlbum.AlbumId}')";
                bResult = DatabaseFactory.GetDatabase().ExecNonQuerySQL(sql);
            }
            catch (Exception)
            {

            }
            return bResult;
        }
        /// <summary>
        /// 歌手信息
        /// </summary>
        /// <param name="SingerName"></param>
        /// <returns></returns>
        public North_Singer GetSingerId(string SingerName)
        {
            North_Singer singer = new North_Singer();
            string sql = $"select * from North_Singer where  SingerName='{SingerName}'";
            var dt = DatabaseFactory.GetDatabase().ExecQuerySQL(sql);
            if (dt != null && dt.Rows.Count == 1)
            {
                singer.SingerId = Convert.ToInt32(dt.Rows[0]["SingerId"]);
            }
            return singer;
        }
        /// <summary>
        /// 获取音乐ID
        /// </summary>
        /// <param name="MusicName"></param>
        /// <returns></returns>
        public North_Music GetMusicId(string MusicName)
        {
            North_Music music = new North_Music();
            string sql = $"select * from North_Music where  MusicName='{MusicName}'";
            var dt = DatabaseFactory.GetDatabase().ExecQuerySQL(sql);
            if (dt != null && dt.Rows.Count == 1)
            {
                music.MusicId = Convert.ToInt32(dt.Rows[0]["MusicId"]);
            }
            return music;
        }
        public bool IsExistSinger(string singerName)
        {
            bool bResult = false;
            try
            {
                string sql = $"select count(*) from North_Singer where SingerName='{singerName}'";
                var info = DatabaseFactory.GetDatabase().ExecuteDataScalarSQL(sql);
                if(info != null &&(int)info!=0)
                {
                    bResult = true;
                }
            }
            catch (Exception)
            {

            }
            return bResult;
        }
        /// <summary>
        /// 添加一个专辑
        /// </summary>
        /// <param name="album"></param>
        /// <returns></returns>
        public int AddAAlbum(North_Album album)
        {
            int bResult = 0;
            try
            {
                string sql = $"insert into North_Album (AlbumName) values('{album.AlbumName}')";
                bResult = DatabaseFactory.GetDatabase().ExecNonQuerySQL(sql);
            }
            catch (Exception)
            {

            }
            return bResult;
        }
        /// <summary>
        /// 查询专辑信息
        /// </summary>
        /// <param name="albumName"></param>
        /// <returns></returns>
        public North_Album GetAlbumId(string albumName)
        {
            North_Album album = new North_Album();
            string sql = $"select * from North_Album where AlbumName='{albumName}'";
            var dt = DatabaseFactory.GetDatabase().ExecQuerySQL(sql);
            if(dt!=null && dt.Rows.Count == 1)
            {
                album.AlbumId =Convert.ToInt32(dt.Rows[0]["AlbumId"]);
            }
            return album;
        }
        public bool IsExistAlbum(string albumName)
        {
            bool bResult = false;
            try
            {
                string sql = $"select count(*) from North_Album where AlbumName='{albumName}'";
                var info = DatabaseFactory.GetDatabase().ExecuteDataScalarSQL(sql);
                if (info != null && (int)info != 0)
                {
                    bResult = true;
                }
            }
            catch (Exception)
            {

            }
            return bResult;
        }
        /// <summary>
        /// 是否存在此音乐信息
        /// </summary>
        /// <param name="musicName"></param>
        /// <returns></returns>
        public bool IsExistMusic(string musicName)
        {
            bool bResult = false;
            try
            {
                string sql = $"select count(*) from North_Music where MusicName='{musicName}'";
                var info = DatabaseFactory.GetDatabase().ExecuteDataScalarSQL(sql);
                if (info != null && (int)info != 0)
                {
                    bResult = true;
                }
            }
            catch (Exception)
            {

            }
            return bResult;
        }

        public List<North_Genre> GetAllGenres()
        {
            List<North_Genre> genres = new List<North_Genre>();
            string sql = "select * from North_Genre";
            using (SqlDataReader dr = DatabaseFactory.GetDatabase().ExecuteDataReaderSQL(sql))
            {
                while (dr.Read())
                {
                    North_Genre list = new North_Genre();
                    list = GetNorthGenreModels(dr);
                    genres.Add(list);
                }
            }
            return genres;
        }

        private North_Genre GetNorthGenreModels(SqlDataReader dr)
        {
            North_Genre model = null;
            if (dr == null)
            {
            }
            else
            {
                IDatabase database = DatabaseFactory.GetDatabase();
                model = new North_Genre();
                model.GenreId = database.GetString(dr, "GenreId");
                model.GenreName = database.GetString(dr, "GenreName");
            }
            return model;
        }
        public List<North_Theme> GetAllThemes()
        {
            List<North_Theme> genres = new List<North_Theme>();
            string sql = "select * from North_Genre";
            using (SqlDataReader dr = DatabaseFactory.GetDatabase().ExecuteDataReaderSQL(sql))
            {
                while (dr.Read())
                {
                    North_Theme list = new North_Theme();
                    list = GetNorthThemeModels(dr);
                    genres.Add(list);
                }
            }
            return genres;
        }

        private North_Theme GetNorthThemeModels(SqlDataReader dr)
        {
            North_Theme model = null;
            if (dr == null)
            {
            }
            else
            {
                IDatabase database = DatabaseFactory.GetDatabase();
                model = new North_Theme();
                model.ThemeId = database.GetString(dr, "ThemeId");
                model.ThemeName = database.GetString(dr, "ThemeName");
            }
            return model;
        }
        public List<North_Country> GetAllCountrys()
        {
            List<North_Country> Country = new List<North_Country>();
            string sql = "select * from North_Country";
            using (SqlDataReader dr = DatabaseFactory.GetDatabase().ExecuteDataReaderSQL(sql))
            {
                while (dr.Read())
                {
                    North_Country list = new North_Country();
                    list = GetNorthCountryModels(dr);
                    Country.Add(list);
                }
            }
            return Country;
        }

        private North_Country GetNorthCountryModels(SqlDataReader dr)
        {
            North_Country model = null;
            if (dr == null)
            {
            }
            else
            {
                IDatabase database = DatabaseFactory.GetDatabase();
                model = new North_Country();
                model.CountryId = database.GetString(dr, "CountryId");
                model.CountryName = database.GetString(dr, "CountryName");
                model.CountryImg = database.GetString(dr, "CountryImg");
            }
            return model;
        }
    }
}
